package com.cms.manage.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author yinrenjie
 * @date 2019-05-13 11:24
 * @description
 */
public class ExcelExportUtil {

    public static Map<String, Object> export(String sheetName, List<String> columnNames, List<String> columnKeys, List<Map<String, Object>> datas) {
        Map<String, Object> result = new HashMap<>();
        result.put("status", 0);
        result.put("message", null);
        result.put("workbook", null);

        HSSFWorkbook workbook = new HSSFWorkbook();

        HSSFFont fontTitle = workbook.createFont();
        fontTitle.setBold(true);
        HSSFCellStyle styleTitle = workbook.createCellStyle();
        styleTitle.setFont(fontTitle);
        styleTitle.setAlignment(HorizontalAlignment.CENTER);

        HSSFCellStyle styleContent = workbook.createCellStyle();
        styleContent.setAlignment(HorizontalAlignment.LEFT);

        HSSFSheet sheet = workbook.createSheet(sheetName);
        int rowIndex = 0;
        HSSFRow row = sheet.createRow(rowIndex++);
        int columnIndex = 0;
        for (String columnName : columnNames) {
            HSSFCell cell = row.createCell(columnIndex++);
            cell.setCellStyle(styleTitle);
            cell.setCellValue(columnName);
        }
        for (Map<String, Object> data : datas) {
            columnIndex = 0;
            row = sheet.createRow(rowIndex++);
            for (String columnKey : columnKeys) {
                String cellValue = String.valueOf(data.get(columnKey));
                if (StringUtils.contains(columnKey, "|")) {
                    cellValue = "";
                    String[] split = columnKey.split("\\|");
                    for (String s : split) {
                        cellValue += data.get(s);
                    }
                }
                HSSFCell cell = row.createCell(columnIndex++);
                cell.setCellStyle(styleContent);
                cell.setCellValue(cellValue);
            }
        }

        result.put("workbook", workbook);
        return result;
    }

    public static Map<String, Object> merge(HSSFWorkbook workbook, String sheetName, List<String> flagColNames, List<String> mergeColNames) {
        Map<String, Object> result = new HashMap<>();
        result.put("status", 0);
        result.put("message", null);

        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFSheet sheet = workbook.getSheet(sheetName);
        HSSFRow rowHead = sheet.getRow(0);
        int lastRowNum = sheet.getLastRowNum();
        int lastColNum = rowHead.getLastCellNum() - 1;
        List<Short> flagColIndexs = new ArrayList<>();
        List<Short> mergeColIndexs = new ArrayList<>();

        for (short colIndex = 0; colIndex <= lastColNum; colIndex++) {
            HSSFCell cell = rowHead.getCell(colIndex);
            String cellValue = cell.getStringCellValue();
            if (cell == null) {
                continue;
            }
            for (String flagColName : flagColNames) {
                if (StringUtils.equals(flagColName, cellValue)) {
                    flagColIndexs.add(colIndex);
                }
            }
            for (String mergeColName : mergeColNames) {
                if (StringUtils.equals(mergeColName, cellValue)) {
                    mergeColIndexs.add(colIndex);
                }
            }
        }

        List<Map<String, Integer>> mergeRows = new ArrayList<>();//[{start:1,end:2},{start:3,end:5}]
        String lastCellValue = getStringCellValue(sheet.getRow(1), flagColIndexs);
        Map<String, Integer> m = new HashMap<>();
        m.put("start", 1);
        for (int rowIndex = 2; rowIndex <= lastRowNum; rowIndex++) {
            String flagCellValue = getStringCellValue(sheet.getRow(rowIndex), flagColIndexs);

            boolean cellValueChanged = !StringUtils.equals(lastCellValue, flagCellValue);
            if (cellValueChanged) {
                m.put("end", rowIndex - 1);
                mergeRows.add(m);
                m = new HashMap<>();
                m.put("start", rowIndex);

                lastCellValue = flagCellValue;
            }
        }
        if (lastRowNum > m.get("start")) {
            m.put("end", lastRowNum);
            mergeRows.add(m);
        }


        for (short mergeColIndex : mergeColIndexs) {
            for (Map<String, Integer> mergeRow : mergeRows) {
                int start = mergeRow.get("start");
                int end = mergeRow.get("end");
                if (start < end) {
                    sheet.addMergedRegion(new CellRangeAddress(start, end, mergeColIndex, mergeColIndex));
                    sheet.getRow(start).getCell(mergeColIndex).setCellStyle(cellStyle);
                }
            }
        }

        return result;
    }

    private static String getStringCellValue(Row row, List<Short> colIndexs) {
        StringBuilder str = new StringBuilder();
        for (Short colIndex : colIndexs) {
            str.append(row.getCell(colIndex).getStringCellValue());
        }
        return str.toString();
    }

    public static Map<String, Object> validator(String sheetName, List<String> columnNames, List<String> columnKeys) {
        Map<String, Object> result = new HashMap<>();
        result.put("status", 0);
        result.put("message", null);

        if (StringUtils.isBlank(sheetName)) {
            result.put("status", 1);
            result.put("message", "sheetName不能为空");
            return result;
        }

        if (null == columnNames || null == columnKeys) {
            result.put("status", 1);
            result.put("message", "columnNames及columnKeys不能为空");
            return result;
        } else if (columnNames.size() != columnKeys.size()) {
            result.put("status", 1);
            result.put("message", "columnNames与columnKeys数量必须相同");
            return result;
        }

        return result;
    }

    public static void main(String[] args) {
        String[] split = "a|b|c".split("\\|");
        for (String s : split) {
            System.out.println(s);
        }
    }
}
